using System;
using System.Collections.Generic;
using System.Text;
using CrudI.Interface;
using System.Data;
using System.Data.SqlClient;
using CrudI;

namespace SQLCodeCreator.DBInfo.SqlServer
{
    public class SqlColumn : IColumn
    {
        string _ColumnName;
        string _FullDataType;
        string _DataType;
        bool _IsNullable;
        bool _isPrimaryKey;
        bool _isFK;
        int _OrdinalPosition;
        string _ColumnDefault;
        List<IConstraint> _Constraint = new List<IConstraint>();
        
        public bool IsFK
        {
            get { return _isFK; }
            set { _isFK = value; }
        }

        //
        public List<IConstraint> Constraint
        {
            
            get { return _Constraint; }
            set { _Constraint = value; }
        }
        #region IColumn Members

        public string ColumnName
        {
            get { return _ColumnName; }
            set { _ColumnName = value; }
        }

        public string DataType
        {
            get { return _DataType; }
            set { _DataType = value; }
        }

        public bool IsNullable
        {
            get { return _IsNullable; }
            set { _IsNullable = value; }
        }
        public bool IsPK
        {
            get { return _isPrimaryKey; }
            set { _isPrimaryKey = value; }
        }

        public int OrdinalPosition
        {
            get { return _OrdinalPosition; }
            set { _OrdinalPosition = value; }
        }
        #endregion

         public SqlColumn(string dataType, string colName, bool isPrimaryKey, bool isNullable)
        {
            _IsNullable = isNullable;
            _isPrimaryKey = isPrimaryKey;
            _ColumnName = colName;
        }

        public SqlColumn()
        {
            
        }

        private static void AddConstraintsToColumn(List<IConstraint> ConstraintList, List<IColumn> columnList)
        {

            for (int column = 0; column < columnList.Count; column++)
            {
                for (int constraint = 0; constraint < ConstraintList.Count; constraint++)
                {
                    if (ConstraintList[constraint].ColumnName ==  columnList[column].ColumnName)
                    {
                        columnList[column].Constraint.Add(ConstraintList[constraint]);
                        if (ConstraintList[constraint].ConType == ConstraintTypes.PK)
                            columnList[column].IsPK = true;
                        else if (ConstraintList[constraint].ConType == ConstraintTypes.FK)
                            columnList[column].IsFK = true;                        
                    }
                }
            }
        }


        internal static List<IColumn> ConvertDataTableToColumnObject(DataTable dt)
        {
            // COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS

            List<IColumn> columnList = new List<IColumn>();
            SqlColumn column;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                column = new SqlColumn();
                if (dt.Columns.Contains("COLUMN_NAME"))
                {

                    column.ColumnName = dt.Rows[i]["COLUMN_NAME"].ToString();
                }
                if (dt.Columns.Contains("ORDINAL_POSITION"))
                {

                }
                if (dt.Columns.Contains("COLUMN_DEFAULT"))
                {
                }
                if (dt.Columns.Contains("IS_NULLABLE"))
                {
                    if (dt.Rows[i]["IS_NULLABLE"] != DBNull.Value)
                    {
                        string test = dt.Rows[i]["IS_NULLABLE"].ToString();
                        if (dt.Rows[i]["IS_NULLABLE"].ToString() == "NO")
                            column.IsNullable = false;
                        else column.IsNullable = true;

                    }
                    else column.IsNullable = false;
                }
                if (dt.Columns.Contains("DATA_TYPE"))
                {
                    column.DataType = dt.Rows[i]["DATA_TYPE"].ToString();
                }
                columnList.Add(column);
            }
            return columnList;
        }

        internal static List<IColumn> GetColumns(string tableName)
        {
            string sqlSelectDatabases = "SELECT COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName ";

            DataTable dt = new DataTable("dtColumns");
            SqlConnection conn = new SqlConnection(GlobaVars.sqlConnection.ToString());
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sqlSelectDatabases;
            cmd.Parameters.AddWithValue("@TableName", tableName);
            try
            {
                cmd.Connection.Open();
                IDataReader rd = cmd.ExecuteReader();
                dt.Load(rd);
            }
            catch (Exception ex)
            {
                throw new DataException("Error opening server for DB select", ex);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            List<IColumn> cols = ConvertDataTableToColumnObject(dt);

            List<IConstraint> constraintList = SqlServerConstraint.GetTableFkPkConstraints(tableName);

            AddConstraintsToColumn(constraintList, cols);

            return cols;
        }
    }
}
